SQL Server高级进阶之索引碎片维护

您所在的位置：网站首页 › fragment fragmentation区别 › SQL Server高级进阶之索引碎片维护

SQL Server高级进阶之索引碎片维护

2023-08-10 13:53| 来源: 网络整理| 查看: 265

一、产生原因及影响

索引是数据库引擎中针对表(有时候也针对视图)建立的特别数据结构，用来帮助查找和整理数据，它的重要性体现在能够使数据库引擎快速返回查询结果。当对索引所在的基础数据表进行增删改时，若存储的数据进行了不适当的跨页（SQL Server中存储的最小单位是页，页是不可再分的），就会导致索引碎片的产生。随着索引碎片的不断增多，查询响应时间就会变慢，性能也因此而下降。要解决这个问题，可以通过重新生成或重新组织索引来解决。

二、碎片分类

2.1、外部碎片

当索引页不在逻辑顺序上时就会产生外部碎片。索引创建时，索引键按照逻辑顺序放在一组索引页上。当新数据插入索引时，新的键可能放在存在的键之间。为了让新的键按照正确的顺序插入，可能会创建新的索引页来存储需要移动的那些存在的键。这些新的索引页通常物理上不会和那些被移动的键原来所在的页相邻。创建新页的过程会引起索引页偏离逻辑顺序。

2.2、内部碎片

当索引页没有用到最大量时就产生了内部碎片。虽然在一个有频繁数据插入的应用程序里这也许有帮助，然而设置一个fill factor（填充因子）会在索引页上留下空间，服务器内部碎片会导致索引尺寸增加，从而在返回需要的数据时要执行额外的读操作。这些额外的读操作会降低查询的性能。

三、维护方法

1、删除索引并重建。

2、使用DROP_EXISTING语句重建索引。

3、使用ALTER INDEX REBUILD重新生成索引。(推荐)

4、使用ALTER INDEX REORGANIZE重新组织索引。(推荐)

四、注意事项碎片率采用方法 >30% ALTER INDEX REBUILD WITH(ONLINE = ON) >5% 且 30 --AND A.AVG_FRAGMENTATION_IN_PERCENT>5 AND A.AVG_FRAGMENTATION_IN_PERCENT30 THEN N'重新生成索引' ELSE N'重新组织索引' END 处理方式, 'ALTER INDEX '+QUOTENAME(B.NAME)+' ON '+QUOTENAME(OBJECT_SCHEMA_NAME(B.OBJECT_ID))+'.'+QUOTENAME(OBJECT_NAME(B.OBJECT_ID))+' ' +CASE WHEN A.AVG_FRAGMENTATION_IN_PERCENT>30 THEN 'REBUILD' ELSE 'REORGANIZE' END 生成SQL语句 FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) A INNER JOIN sys.indexes B ON A.OBJECT_ID=B.OBJECT_ID AND A.INDEX_ID=B.INDEX_ID WHERE A.AVG_FRAGMENTATION_IN_PERCENT>5 AND B.INDEX_ID>0 --AND OBJECT_NAME(B.OBJECT_ID) IN ('INVMB') --指定表 ORDER BY CASE WHEN A.AVG_FRAGMENTATION_IN_PERCENT>30 THEN N'重新生成索引' ELSE N'重新组织索引' END,OBJECT_NAME(B.OBJECT_ID),B.INDEX_ID

注：将【生成SQL语句】拷贝出来执行即可。

6.2、自动方式

第一步：在服务中启动SQL Server 代理。